package com.gfwy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.gfwy.dao.IEssayDao;
import com.gfwy.vo.Essay;

public class EssayDaoImpl implements IEssayDao {
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	public EssayDaoImpl(Connection conn) {
		this.conn = conn;
	}
	@Override
	public boolean doCreate(Essay essay) throws Exception {
		boolean flag = false;
		try {
			String sql = "INSERT INTO essay(ename,author,content,pubdate,cid) VALUES(?,?,?,?,?)";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, essay.getEname());
			this.pstmt.setString(2, essay.getAuthor());
			this.pstmt.setString(3, essay.getContent());
			this.pstmt.setTimestamp(4, new java.sql.Timestamp(essay.getPubdate().getTime()));
			this.pstmt.setInt(5, essay.getCid());
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	@Override
	public boolean doUpdate(Essay essay) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE essay SET ename=?,author=?,content=?,pubdate=?,cid=? WHERE eid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, essay.getEname());
			this.pstmt.setString(2, essay.getAuthor());
			this.pstmt.setString(3, essay.getContent());
			this.pstmt.setTimestamp(4, new java.sql.Timestamp(essay.getPubdate().getTime()));
			this.pstmt.setInt(5, essay.getCid());
			this.pstmt.setInt(6, essay.getEid());
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	@Override
	public boolean doDelete(int eid) throws Exception {
		boolean flag = false;
		try {
			String sql = "DELETE FROM essay WHERE eid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, eid);
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

	@Override
	public int count(String keyWord) throws Exception {
		int count = 0;
		keyWord = (keyWord == null) ? "" : keyWord;
		try {
			String sql = "SELECT COUNT(*) AS count FROM essay WHERE ename LIKE ? OR content LIKE ?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, "%" + keyWord + "%");
			this.pstmt.setString(2, "%" + keyWord + "%");
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				count = this.rs.getInt("count");
				
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		System.out.println(count);
		return count;
	}

	@Override
	public List<Essay> findAll(String keyWord, int startIndex, int count)
			throws Exception {
		List<Essay> list = null;
		keyWord = (keyWord == null) ? "" : keyWord;
		try {
			String sql = "SELECT period.*,chapter.*,essay.* FROM essay " +
					"LEFT JOIN chapter ON chapter.cid=essay.cid " +
					"LEFT JOIN period ON period.pid=chapter.pid " +
					"WHERE period.pname LIKE ? OR chapter.cname LIKE ? OR essay.ename LIKE ? OR essay.content LIKE ? " +
					"ORDER BY essay.pubdate DESC LIMIT ?,?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setString(1, "%" + keyWord + "%");
			this.pstmt.setString(2, "%" + keyWord + "%");
			this.pstmt.setString(3, "%" + keyWord + "%");
			this.pstmt.setString(4, "%" + keyWord + "%");
			this.pstmt.setInt(5, startIndex);
			this.pstmt.setInt(6, count);
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<Essay>();
			while(this.rs.next()) {
				Essay e = new Essay();
				e.setEid(this.rs.getInt("essay.eid"));
				e.setEname(this.rs.getString("essay.ename"));
				e.setContent(this.rs.getString("essay.content"));
				e.setAuthor(this.rs.getString("essay.author"));
				e.setPubdate(this.rs.getDate("essay.pubdate"));
				e.setCid(this.rs.getInt("chapter.cid"));
				e.setCname(this.rs.getString("chapter.cname"));
				e.setPname(this.rs.getString("period.pname"));
				e.setPid(this.rs.getInt("period.pid"));
				list.add(e);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}

	@Override
	public Essay findById(int eid) throws Exception {
		Essay essay = null;
		try {
			String sql = "SELECT period.*,chapter.*,essay.* FROM essay " +
					"LEFT JOIN chapter ON chapter.cid=essay.cid " +
					"LEFT JOIN period ON period.pid=chapter.pid " +
					"WHERE essay.eid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, eid);
			this.rs = this.pstmt.executeQuery();
			if(this.rs.next()) {
				essay = new Essay();
				essay.setEid(this.rs.getInt("essay.eid"));
				essay.setEname(this.rs.getString("essay.ename"));
				essay.setContent(this.rs.getString("essay.content"));
				essay.setAuthor(this.rs.getString("essay.author"));
				essay.setPubdate(this.rs.getDate("essay.pubdate"));
				essay.setCid(this.rs.getInt("chapter.cid"));
				essay.setCname(this.rs.getString("chapter.cname"));
				essay.setPname(this.rs.getString("period.pname"));
				essay.setPid(this.rs.getInt("period.pid"));
				essay.setClick(this.rs.getInt("essay.click"));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return essay;
	}
	@Override
	public List<Essay> findOrderByClick() throws Exception {
		List<Essay> list = null;
		try {
			String sql = "SELECT * FROM essay ORDER BY click DESC LIMIT 0, 5";
			this.pstmt = this.conn.prepareStatement(sql);
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<Essay>();
			while(this.rs.next()) {
				Essay essay = new Essay();
				essay.setEid(this.rs.getInt("eid"));
				essay.setEname(this.rs.getString("ename"));
				essay.setClick(this.rs.getInt("click"));
				list.add(essay);
			}
			
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}
	@Override
	public List<Essay> findReferences(int eid, int cid, int size)
			throws Exception {
		List<Essay> list = null;
		try {
			String sql = "SELECT * FROM essay WHERE eid <> ? AND cid = ? LIMIT 0, ?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, eid);
			this.pstmt.setInt(2, cid);
			this.pstmt.setInt(3, size);
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<Essay>();
			while(this.rs.next()) {
				Essay essay = new Essay();
				essay.setEid(this.rs.getInt("eid"));
				essay.setEname(this.rs.getString("ename"));
				essay.setContent(this.rs.getString("content"));
				essay.setAuthor(this.rs.getString("author"));
				essay.setPubdate(this.rs.getDate("pubdate"));
				list.add(essay);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}
	@Override
	public List<Essay> findByCid(int cid) throws Exception {
		List<Essay> list = null;
		try {
			String sql = "SELECT * FROM essay WHERE cid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, cid);
			this.rs = this.pstmt.executeQuery();
			list = new ArrayList<Essay>();
			while(this.rs.next()) {
				Essay essay = new Essay();
				essay.setEid(this.rs.getInt("eid"));
				essay.setEname(this.rs.getString("ename"));
				essay.setContent(this.rs.getString("content"));
				essay.setAuthor(this.rs.getString("author"));
				essay.setPubdate(this.rs.getDate("pubdate"));
				list.add(essay);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.rs != null) {
				try {
					this.rs.close();
				} catch (Exception e) {
					throw e;
				}
			}
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return list;
	}
	@Override
	public boolean doIncreaseClick(int eid) throws Exception {
		boolean flag = false;
		try {
			String sql = "UPDATE essay SET click=click+1 WHERE eid=?";
			this.pstmt = this.conn.prepareStatement(sql);
			this.pstmt.setInt(1, eid);
			if(this.pstmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if(this.pstmt != null) {
				try {
					this.pstmt.close();
				} catch (Exception e) {
					throw e;
				}
			}
		}
		return flag;
	}

}
